﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Model;
namespace DAL
{
    public class DALSubjectInfo
    {
        #region SQL CONST
        private const string INSERT_SQL = "INSERT INTO [SubjectInfo] ([SubjectName],[Score]) Values (@SubjectName,@Score)";
        private const string UPDATE_SQL = "UPDATE [SubjectInfo] SET SubjectName=@SubjectName,Score=@Score WHERE [SubjectNo]=@SubjectNo";
        private const string DEL_SQL = "DELETE FROM [SubjectInfo] WHERE [SubjectNo]=@SubjectNo";
        private const string SELECT_ALL_SQL = "SELECT [SubjectNo],[SubjectName],[Score] FROM [SubjectInfo] order by subjectNo desc";
        private const string SELECT_ONE_SQL = "SELECT [SubjectNo],[SubjectName],[Score] FROM [SubjectInfo] WHERE [SubjectNo]=@SubjectNo";
        private const string SELECT_WHERE_SQL = "SELECT [SubjectNo],[SubjectName],[Score] FROM [SubjectInfo] {$where} order by  subjectNo desc";
        #endregion
        #region Paras const
        private const string PARA_SubjectNo = "@SubjectNo";
        private const string PARA_SubjectName = "@SubjectName";
        private const string PARA_Score = "@Score";
        #endregion
        #region Add Method
        /// <summary>
        /// Insert an item
        /// </summary>
        /// <param name="SubjectInfo"></param>
        /// <returns>count of rows affected</returns>
        public int AddSubjectInfo(SubjectInfoEntity SubjectInfo)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] SubjectInfoParms = GetSubjectInfoParametersInsert();
            //set values to paras
            SubjectInfoParms[0].Value = SubjectInfo.SubjectName;
            SubjectInfoParms[1].Value = SubjectInfo.Score;
            //insert			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, INSERT_SQL, SubjectInfoParms);
            }
            return backvalue;
        }
        #endregion
        #region Mod Method
        /// <summary>
        /// Update an item
        /// </summary>
        /// <param name="SubjectInfo"></param>
        /// <returns>count of rows affected</returns>
        public int ModSubjectInfo(SubjectInfoEntity SubjectInfo)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] SubjectInfoParms = GetSubjectInfoParameters();
            //set values to paras
            SubjectInfoParms[0].Value = SubjectInfo.SubjectNo;
            SubjectInfoParms[1].Value = SubjectInfo.SubjectName;
            SubjectInfoParms[2].Value = SubjectInfo.Score;
            //update			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, UPDATE_SQL, SubjectInfoParms);
            }
            return backvalue;
        }
        #endregion
        #region Del Method
        /// <summary>
        /// Del an item by primary key
        /// </summary>
        /// <param name="subjectNo"></param> 
        /// <returns>count of rows affected</returns>
        public int DelSubjectInfo(int subjectNo)
        {
            int backvalue = 0;
            //primarykeys
            //get paras
            SqlParameter[] SubjectInfoParms = GetSubjectInfoPrimaryKeyParameters();
            //set values to paras
            SubjectInfoParms[0].Value = subjectNo;
            //delete
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, DEL_SQL, SubjectInfoParms);
            }
            return backvalue;
        }
        #endregion
        #region Select Method
        /// <summary>
        /// Select an item by Primarykey
        /// </summary>
        /// <param name="subjectNo"></param>
        /// <returns>an item</returns>
        public SubjectInfoEntity GetSubjectInfo(int subjectNo)
        {
            SubjectInfoEntity SubjectInfo = new SubjectInfoEntity();
            //primarykeys
            //get paras
            SqlParameter[] SubjectInfoParms = GetSubjectInfoPrimaryKeyParameters();
            //set values to paras
            SubjectInfoParms[0].Value = subjectNo;
            //select
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ONE_SQL, SubjectInfoParms))
            {
                if (reader.Read())
                {
                    if (!reader.IsDBNull(0)) SubjectInfo.SubjectNo = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) SubjectInfo.SubjectName = reader.GetString(1);
                    if (!reader.IsDBNull(2)) SubjectInfo.Score = reader.GetInt32(2);
                }
                else
                {
                    SubjectInfo = null;
                }
            }
            return SubjectInfo;

        }
        #endregion
        #region Select_All Method
        /// <summary>
        /// Select all items
        /// </summary>
        /// <returns>items</returns>
        public IList<SubjectInfoEntity> GetSubjectInfos()
        {
            IList<SubjectInfoEntity> SubjectInfos = new List<SubjectInfoEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                while (reader.Read())
                {
                    SubjectInfoEntity SubjectInfo = new SubjectInfoEntity();
                    if (!reader.IsDBNull(0)) SubjectInfo.SubjectNo = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) SubjectInfo.SubjectName = reader.GetString(1);
                    if (!reader.IsDBNull(2)) SubjectInfo.Score = reader.GetInt32(2);
                    SubjectInfos.Add(SubjectInfo);
                }
            }
            return SubjectInfos;
        }
        #endregion
        #region private common select by conditions
        /// <summary>
        /// private common select by conditions
        /// </summary>
        /// <returns>IList<SubjectInfoEntity></returns>
        private IList<SubjectInfoEntity> GetSubjectInfosbyQueryString(string SQLstr)
        {
            IList<SubjectInfoEntity> SubjectInfos = new List<SubjectInfoEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQLstr))
            {
                while (reader.Read())
                {
                    SubjectInfoEntity SubjectInfo = new SubjectInfoEntity();
                    if (!reader.IsDBNull(0)) SubjectInfo.SubjectNo = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) SubjectInfo.SubjectName = reader.GetString(1);
                    if (!reader.IsDBNull(2)) SubjectInfo.Score = reader.GetInt32(2);
                    SubjectInfos.Add(SubjectInfo);
                }
            }
            return SubjectInfos;
        }
        #endregion
        #region public common select by conditions ,we can use it by changing the GetSubjectInfosbyCondition(datatype condition) and sql
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<SubjectInfoEntity></returns>
        public IList<SubjectInfoEntity> GetSubjectInfosbyCondition(string sql)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SELECT_WHERE_SQL);
            sb.Replace("{$where}", "where " + sql);
            return GetSubjectInfosbyQueryString(sb.ToString());
        }
        #endregion
        #region Select Method Top n
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<SubjectInfoEntity></returns>
        public IList<SubjectInfoEntity> GetSubjectInfosTopN(int n)
        {
            IList<SubjectInfoEntity> SubjectInfos = new List<SubjectInfoEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                int i = 0;
                while (reader.Read() && i < n)
                {
                    SubjectInfoEntity SubjectInfo = new SubjectInfoEntity();
                    if (!reader.IsDBNull(0)) SubjectInfo.SubjectNo = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) SubjectInfo.SubjectName = reader.GetString(1);
                    if (!reader.IsDBNull(2)) SubjectInfo.Score = reader.GetInt32(2);
                    SubjectInfos.Add(SubjectInfo);
                    i++;
                }
            }
            return SubjectInfos;
        }
        #endregion
        #region paras configuration
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to insert ,especially autoincrement
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetSubjectInfoParametersInsert()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_SubjectInfo_Insert");

            if (parms == null)
            {
                parms = new SqlParameter[] {
			new SqlParameter(PARA_SubjectName,SqlDbType.NVarChar) ,
			new SqlParameter(PARA_Score,SqlDbType.Int)			};
                SQLHelper.CacheParameters("SQL_ALL_SubjectInfo_Insert", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to update
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetSubjectInfoParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_SubjectInfo_Update");

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARA_SubjectNo,SqlDbType.Int) ,
					new SqlParameter(PARA_SubjectName,SqlDbType.NVarChar) ,
					new SqlParameter(PARA_Score,SqlDbType.Int)					};
                SQLHelper.CacheParameters("SQL_ALL_SubjectInfo_Update", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:primary key paras
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetSubjectInfoPrimaryKeyParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_PrimaryKey_SubjectInfo");

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARA_SubjectNo,SqlDbType.Int) ,
					};
                SQLHelper.CacheParameters("SQL_PrimaryKey_SubjectInfo", parms);
            }
            return parms;
        
        }
        #endregion
    }
}
